package com.yzyz.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.yzyz.bean.book.BookBean;
import com.yzyz.dao.IBookDao;
import com.yzyz.utils.BookStatus;

/**
 * 预订信息数据库操作类
 * @author david.sun
 * @date 2010-7-11
 */
public class BookDaoImpl implements IBookDao {

	private JdbcTemplate jdbcTemplate;
	
	@SuppressWarnings("unchecked")
	@Override
	public List<BookBean> paginate(int start, int limit) {
		String sql = "select * from book order by date desc limit " + start + ", " + limit;
		List<BookBean> list = this.jdbcTemplate.query(sql, new BookRowMapper());
		return list;
	}

	@Override
	public int delete(int id) {
		
		return this.jdbcTemplate.update("delete from book where id=" + id);
	}

	@SuppressWarnings("unchecked")
	@Override
	public Object get(int id) {
		List<BookBean> list = this.jdbcTemplate.query("select * from book where id=" + id, new BookRowMapper());
		if(list == null || list.size() != 1) {
			return null;
		}
		return list.get(0);
	}

	@Override
	public int getCount(int ownerId) {
		
		return this.jdbcTemplate.queryForInt("select count(*) from book");
	}

	@Override
	public int save(Object object) {
		final BookBean book = (BookBean)object;
		final String sql = "insert into book (name, date, routeId, memo, email, phoneNumber) values(?,?,?,?,?,?)";
		KeyHolder key = new GeneratedKeyHolder();
		this.jdbcTemplate.update(new PreparedStatementCreator(){

			@Override
			public PreparedStatement createPreparedStatement(Connection conn)
					throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				ps.setString(1, book.getName());
				ps.setTimestamp(2, new java.sql.Timestamp(book.getDate().getTime()));
				ps.setInt(3, book.getRouteId());
				ps.setString(4, book.getMemo());
				ps.setString(5, book.getEmail());
				ps.setString(6, book.getPhoneNumber());
				return ps;
			}}, key);
		book.setId(key.getKey().intValue());
		return key.getKey().intValue();
	}

	@Override
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;

	}

	@Override
	public void update(Object object) {
		final BookBean book = (BookBean)object;
		String sql = "update book set status=" + book.getStatus() + " where id=" + book.getId();
		this.jdbcTemplate.update(sql);

	}

	@Override
	public int updateAvailable(int id, boolean available) {
		
		return 0;
	}
	
	private class BookRowMapper implements RowMapper {

		@Override
		public Object mapRow(ResultSet rs, int arg1) throws SQLException {
			BookBean book = new BookBean();
			book.setDate(new Date(rs.getTimestamp("date").getTime()));
			book.setEmail(rs.getString("email"));
			book.setId(rs.getInt("id"));
			book.setMemo(rs.getString("memo"));
			book.setName(rs.getString("name"));
			book.setStatus(BookStatus.getStatus(rs.getInt("status")));
			book.setRouteId(rs.getInt("routeId"));
			return book;
		}
		
	}

}
